--[[
 excellib.lua
 Library for simplified recording and plotting of data in Excel.
 This should be simpler to use that the underlying Excel API.
 
 Example:
   local EX = simion.import 'excellib.lua'
   local t = {{10, 20}, {20,25}, {30,27}, title = 'testing'}
   local plot = EX.plot(t)

   simion.sleep(5) -- wait five seconds before updating again
   t[1][2] = 30    -- change data
   plot:update_data(t)
 
 D.Manura, 2008-05-10, 2011-09-09
 (c) 2008-2011 Scientific Instrument Services, Inc. Licensed under SIMION 8.0/8.1.
--]]

local EXCEL = {}
local Plot = {}

--[[
  Returns true/false whether Excel is installed.
--]]
function EXCEL.is_excel_installed()
  return luacom.CLSIDfromProgID "Excel.Application" ~= nil
end


--[[
  Returns an Excel object.
  Keeps at most one Excel object open at a time (_G.excel).
  Raises on error.
--]]
function EXCEL.get_excel()
  if not EXCEL.is_excel_installed() then
    error("Excel does not appear installed.")
  end
  -- If Excel object exists but doesn't work, perhaps the Excel process
  -- was prematurely terminated.  If so, release the Excel object.
  if _G.excel and not pcall(function() local _ = _G.excel.Visible end) then
    _G.excel = nil
  end
  -- Attempt to create new Excel object if it doesn't exist.
  _G.excel = _G.excel or luacom.CreateObject("Excel.Application")
  if not _G.excel then
    error("Could not create Excel object.")
  end
  return _G.excel
end


local CHART_TYPES = {}
CHART_TYPES.scatter = -4169     -- scatterplot (xlXYScatter)
CHART_TYPES.scatter_lines = 74  -- scatterplot with connecting lines (xlXYScatterLines)
CHART_TYPES.bar_vertical = 51   -- vertical bar chart (xlColumnClustered)
CHART_TYPES.area = 1            -- area chart (xlArea)

--[[
  Plots data from table t in Excel.
  
  t is normally an array of rows of column values.  Alternately, t
  may be an array of arrays of rows of column values.
  t may optionally also contain these fields:

    header - array of column headers.
    title - title for plot.  Defaults to none if omitted.
    xlabel - x label for plot.  Defaults to t.header[1] if omitted.
    ylabel - y label for plot.  Defaults to t.header[2] if omitted.
    chart_type - any chart type name string in CHART_TYPES above
                   (defaults to 'scatter')
    lines - if true and chart_type is nil then chart_type is set to 'scatter_lines'

  Returns a "plot" object, which may subsequently be used to call
  plot:update_data.  It also has "wb" (Excel Workbook Object),
  "ws" (Excel Worksheet object)and "chart" (Excel Chart object) fields.

  Examples:

    -- Plot points (1,2) and (4,5)
    EXCEL.plot {{1,2}, {4,5}}

    -- Plot with additional parameters
    EXCEL.plot {header={'time', 'speed'},
                title='my plot', xlabel='t', ylabel='s', lines=true,
                {1,2}, {4,5}}

    -- Plot two data series having same X values.
    -- First column is X.  Second and third columns are Y for each series.
    EXCEL.plot {{1,2,3}, {4,5,6}}

    -- Plot two data series having possibly different X values.
    -- Two independent sets of data:
    -- First column is X.  Second column is Y.
    EXCEL.plot {{{1,2},{4,5}}, {{2,6},{3,7}}}
--]]
function EXCEL.plot(t)
  local chart_type = t.chart_type

  -- Compatibility with older versions of this library.
  if t.lines then
    assert(chart_type == nil)
    chart_type = 'scatter_lines'
  end

  local excel = EXCEL.get_excel()

  -- Set up worksheet.
  excel.Visible = true
  local wb = excel.Workbooks:Add()
  local ws = wb.Worksheets(1)

  -- Set up chart.  
  local chart = excel.Charts:Add()
  -- Set chart type.
  if chart_type and not CHART_TYPES[chart_type] then
    error("undefined chart type " .. tostring(chart_type), 2)
  end
  chart.ChartType = CHART_TYPES[chart_type or 'scatter']

  local plot = setmetatable({wb=wb, ws=ws, chart=chart}, {__index = Plot})
  
  -- Normalize table.
  local datasets = (t[1] and t[1][1] and type(t[1][1]) ~= 'table') and {t} or t
  
  -- For each dataset...
  local icol = 1
  for _,dataset in ipairs(datasets) do
    -- number of rows and columns in data.
    local nrows = #dataset
    local ncols = #(dataset.header or dataset[1])
  
    -- Transfer header labels and data to Excel.
    if dataset.header then
      ws:Range(ws.Cells(1,icol), ws.Cells(1, icol+ncols-1)).Value2 = dataset.header
    end

    if nrows > 0 then
      -- Define chart data sources.
      for i=1,ncols-1 do
        local series = chart.SeriesCollection(chart):NewSeries()
        series.Name =    ws:Range(ws.Cells(1, icol+i), ws.Cells(1, icol+i))
        series.XValues = ws:Range(ws.Cells(2, icol),   ws.Cells(2+nrows, icol))
        series.Values  = ws:Range(ws.Cells(2, icol+i), ws.Cells(2+nrows, icol+i))
      end
    end

    icol = icol + ncols
  end

  plot:update_data(t)

  -- Define chart options.
  if t.title then
    chart.HasTitle = true
    chart.ChartTitle:Characters().Text = t.title
  end
  local xlabel = t.xlabel or t.header and t.header[1]
  if xlabel then
    chart.Axes(1,1).HasTitle = true
    chart.Axes(1,1).AxisTitle:Characters().Text = xlabel
  end
  local ylabel = t.ylabel or t.header and t.header[2]
  if ylabel then
    chart.Axes(1,2).HasTitle = true
    chart.Axes(1,2).AxisTitle:Characters().Text = ylabel
  end

  wb.Saved = true  -- prevent asking to save.
  
  return plot
end

--[[
  Updates a plot previously returned by the `plot` function.
  Data table `t` is in the same format as in `plot`, except that
  only row/column data values are used.
--]]
function Plot:update_data(t)
  local ws = self.ws
  
  -- Normalize table.
  local datasets = (t[1] and t[1][1] and type(t[1][1]) ~= 'table') and {t} or t

  -- For each dataset...
  local icol = 1
  for _,dataset in ipairs(datasets) do
    -- number or rows and columns in data.
    -- number of rows and columns in data.
    local nrows = #dataset
    local ncols = #(dataset.header or dataset[1])
  
    -- Transfer header labels and data to Excel.
    --OLD: ws:Range(ws.Cells(2,1), ws.Cells(nrows+1, ncols)).Value2 = t
    if nrows > 0 then
      -- workaround for LuaCOM bug http://simion.com/issue/495.2
      -- is to transfer data in chunks.
      -- OLD: ws:Range(ws.Cells(2,icol), ws.Cells(nrows+1, icol+ncols-1)).Value2
      --       = dataset
      local i=1
      while i <= nrows do
        local copy = {}
        for j=1,500 do copy[j] = dataset[i + j - 1] end
        ws:Range(ws.Cells(1+i,icol), ws.Cells(1+i+#copy-1, icol+ncols-1)).Value2 = copy
        i = i + 500
      end
    end

    icol = icol + ncols
  end -- dataset
 
  local wb = ws.Parent
  wb.Saved = true  -- prevent asking to save.
end

--[[
  update plot title
--]]
function Plot:title(name)
  self.chart.ChartTitle:Characters().Text = name
  self.wb.Saved = true -- prevent asking to save above change
end

return EXCEL
